package datamodule;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import util.FString;
import conexao.Connect;

public class ClasseDAO {


	private DBuserVO userVO;

	public ClasseDAO( DBuserVO userVO){
		this.userVO = userVO;
	}
	//Inserir Classe
	public int inserir(ClasseVO VOcla) throws ClassNotFoundException, SQLException {
		Connection con = Connect.getConnection(userVO);
		PreparedStatement pstmtInsert = con.prepareStatement(
				"INSERT INTO classe(cla_Cod_Cla, cla_Descricao) " +
				" VALUES(?,?) " );
		pstmtInsert.setString(1, VOcla.getCla_Cod_Cla());			
		pstmtInsert.setString(2, VOcla.getCla_Descricao());
		int reg = pstmtInsert.executeUpdate();	
		con.close();
		return reg;
	}

	//Alterar Classe
	public int alterar(ClasseVO VOcla) throws ClassNotFoundException, SQLException {
		Connection con = Connect.getConnection(userVO);
		String virgula = "";
		StringBuffer sql = new StringBuffer(" UPDATE classe SET ");
		if (!VOcla.getCla_Cod_Cla().equals(FString.ignorar)) {
			sql.append(virgula+" cla_Cod_Cla = '"+VOcla.getCla_Cod_Cla()+"' ");
			virgula = ",";
		}
		if (!VOcla.getCla_Descricao().equals(FString.ignorar)) {
			sql.append(virgula+" cla_Descricao = '"+VOcla.getCla_Descricao()+"' ");
			virgula = ",";
		}

		sql.append(" WHERE cla_Cod_Cla = '"+VOcla.getCla_Cod_Cla()+"' ");
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0, sql.length()));
		int registrosAfetados = pstmt.executeUpdate();
		con.close();
		return registrosAfetados;
	}	
	
	// Deletar Classe
	public int deletar(ClasseVO VOcla) throws ClassNotFoundException, SQLException {		
		Connection con = Connect.getConnection(userVO);
		PreparedStatement pstmt = con.prepareStatement(
		"DELETE FROM classe WHERE cla_Cod_Cla = ?");
		pstmt.setString(1, VOcla.getCla_Cod_Cla());
		int registrosAfetados = pstmt.executeUpdate();
		con.close();
		return registrosAfetados;
	}

	//Pesquisar Classe
	public ClasseVO pesquisar(ClasseVO VOcla) throws ClassNotFoundException, SQLException {				
		Connection con = Connect.getConnection(userVO);
		StringBuffer sql = new StringBuffer("SELECT * FROM classe Where cla_Cod_Cla <> '' ");
		
		if (!VOcla.getCla_Cod_Cla().equals(FString.ignorar)) {
			sql.append(" AND UPPER(cla_Cod_Cla) = '"+VOcla.getCla_Cod_Cla()+"' ");			
		}		
		if (!VOcla.getCla_Descricao().equals(FString.ignorar)) {
			sql.append(" AND UPPER(cla_Descricao) = '"+VOcla.getCla_Descricao()+"' ");			
		}
	
		//System.out.println(sql);
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0,sql.length()));		
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {

			VOcla = new ClasseVO();
			VOcla.setCla_Cod_Cla( rs.getString("cla_Cod_Cla"));
			VOcla.setCla_Descricao ( rs.getString("cla_Descricao"));
			
		}else {
			VOcla = null;
		}
		pstmt.close();
		rs.close();
		con.close();
		return VOcla;
	}

	public ArrayList<ClasseVO> geral(ClasseVO VOcla) throws SQLException, ClassNotFoundException {		
		ArrayList<ClasseVO> lista = null;
		Connection con = Connect.getConnection(userVO);
		StringBuffer sql = new StringBuffer("SELECT * FROM classe  Where cla_Cod_Cla <> '' ");
		if (!VOcla.getCla_Cod_Cla().equals(FString.ignorar)) {
			sql.append(" AND UPPER(cla_Cod_Cla) LIKE UPPER('"+VOcla.getCla_Cod_Cla()+"')");
		}
		if (!VOcla.getCla_Descricao().equals(FString.ignorar)) {
			sql.append(" AND UPPER(cla_Descricao) LIKE UPPER('"+VOcla.getCla_Descricao()+"%"+"')");
		}
		if (!VOcla.getOrdem().equals(FString.ignorar)) {
			sql.append(" ORDER BY "+VOcla.getOrdem());
		}
		if (!VOcla.getLimit().equals(FString.ignorar)) {
			sql.append(" LIMIT "+VOcla.getLimit());
		}
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0,sql.length()));		
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			lista = new ArrayList<ClasseVO>();
			do {
				ClasseVO his = new ClasseVO();
				his.setCla_Cod_Cla( rs.getString("cla_Cod_Cla"));
				his.setCla_Descricao( rs.getString("cla_Descricao"));
			
				lista.add(his);
			}
			while (rs.next());			
		}
		pstmt.close();
		rs.close();
		con.close();
		return lista;
	}	
	
}